package ua.com.softway.db.export;

import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.Session;

import ua.com.softway.date.Date;
import ua.com.softway.date.DatePattern;
import ua.com.softway.db.Persistence;

public class ExportTable {
    private final String tableName;

    public ExportTable(String tableName) {
        this.tableName = tableName;
    }

    List<FieldExportConstraint> fieldConstraints = new ArrayList<FieldExportConstraint>();
    List<JoinExportConstraint> joinConstraints = new ArrayList<JoinExportConstraint>();

    public ExportTable where(String name, int value) {
        fieldConstraints.add(new FieldExportConstraint(name, value));
        return this;
    }
    
    public ExportTable join(String name, String expression) {
        joinConstraints.add(new JoinExportConstraint(name, expression));
        return this;
    }
    
    public void export(PrintWriter out) throws SQLException {
        delete(out);
        insert(out);
    }

    private void delete(PrintWriter out) {
        out.println("delete `" + tableName + "` from " + fromClause() + whereClause() + ";");
    }
    
    @SuppressWarnings("deprecation")
    private void insert(PrintWriter out) throws SQLException {
        String sql = "select " + tableName + ".* from " + fromClause() + whereClause();
        System.out.println(sql);
        
        Connection connection = ((Session) Persistence.current().getDelegate()).connection(); 
        
        PreparedStatement st = connection.prepareStatement(sql);
        
        ResultSet rs = st.executeQuery();
        
        ResultSetMetaData metaData = rs.getMetaData();

        String s = "insert into `" + tableName + "`(";
        
        for (int i = 1; i <= metaData.getColumnCount(); i ++) {
            if (i > 1) s = s + ",";
            
            s = s + "`" + metaData.getColumnName(i) + "`";
        }
        
        s = s + ") values ";
        
        int count = 0;
        
        while (rs.next()) {
            count ++;
            
            if (!s.endsWith(" ")) s = s + ",";
            s = s + "(";
            
            for (int i = 1; i <= metaData.getColumnCount(); i ++) {
                if (!s.endsWith("(")) s = s + ",";
                
                Date date;
                DatePattern dp;
                
                switch (metaData.getColumnType(i)) {
                case Types.VARCHAR:
                    if (rs.getString(i) == null)
                        s = s + "NULL";
                    else
                        s = s + "'" + escapeStringLiteral(rs.getString(i)) + "'";
                    break;
                    
                case Types.DOUBLE:
                    double dblVal = rs.getDouble(i);
                    
                    if (rs.wasNull())
                        s = s + "NULL";
                    else
                        s = s + dblVal;
                    
                    break;
                    
                case Types.INTEGER:
                    int intVal = rs.getInt(i);
                    
                    if (rs.wasNull())
                        s = s + "NULL";
                    else
                        s = s + intVal;
                    
                    break;
                    
                case Types.DATE:
                    if (rs.getDate(i) == null)
                        s = s + "NULL";
                    else {
                        date = Date.fromJavaDate(rs.getDate(i));
                        dp = new DatePattern("yyyy-MM-dd");
                        
                        s = s + "'" + dp.format(date) + "'";
                    }
                    break;
                    
                case Types.TIMESTAMP:
                    if (rs.getTimestamp(i) == null)
                        s = s + "NULL";
                    else {
                        date = Date.fromJavaDate(rs.getTimestamp(i));
                        dp = new DatePattern("yyyy-MM-dd HH:mm:ss");
                        
                        s = s + "'" + dp.format(date) + "'";
                    }
                    break;
                    
                case Types.BIT:
                    int boolVal = rs.getInt(i);
                    
                    if (rs.wasNull())
                        s = s + "NULL";
                    else
                        s = s + boolVal;
                    
                    break;
                    
                default:
                    throw new RuntimeException("Unsupported SQL type " + metaData.getColumnType(1 + i));
                }
            }
            s = s + ")";
        }
        
        s = s + ";";
        
        if (count > 0)
            out.println(s);
    }

    private String escapeStringLiteral(String s) {
        return s.replaceAll("'", "''");
    }

    private String whereClause() {
        if (fieldConstraints.isEmpty()) return "";
        
        String s = " where ";
        
        for (FieldExportConstraint c : fieldConstraints) {
            if (!s.equals(" where ")) s = s + " and ";
            
            s = s + c.whereClause();
        }
        
        return s;
    }
    
    private String fromClause() {
        String s = tableName;

        for (JoinExportConstraint c : joinConstraints)
            s = s + c.fromClause();
        
        return s;
    }
}